﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using yncTech.Common;
using yncTech.BusinessObjects;
using System.Globalization;
using System.Text;
using System.Data.OleDb;
using System.Collections;

using EntitySpaces.Interfaces;

public partial class Admin_SAPFin_SAPFin001a : AdminBasePage
{
    Hashtable hsColumn;
    protected void Page_Load(object sender, EventArgs e)
    {
        hsColumn = new Hashtable();
        hsColumn.Add("Company Code", -1);
        hsColumn.Add("Document Number", -1);
        hsColumn.Add("Reference", -1);
        hsColumn.Add("Reference Key", -1);
        hsColumn.Add("Document Header Text", -1);
        hsColumn.Add("Posting Date", -1);
        hsColumn.Add("Document Date", -1);
        hsColumn.Add("Reversed with", -1);
        hsColumn.Add("User name", -1);
        hsColumn.Add("Document Type", -1);
        hsColumn.Add("Reference Transact.", -1);
        hsColumn.Add("Logical System", -1);
        hsColumn.Add("Entry Date", -1);
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        lbMessage.Text = "";
        if (!fileUpload.HasFile)
        {
            lbMessage.Text = "请选择要上传的excel文件";
            return;
        }
        Session["excelFile"] = fileUpload.FileName;
        string fileName = Guid.NewGuid().ToString() + Path.GetExtension(fileUpload.FileName);
        Session["fileName"] = fileName;

        if (fileUpload.FileName.Contains("_"))
        {
            txtHidDocSys.Value = fileUpload.FileName.Split('_')[0];
            txtHidCompanyName.Value = fileUpload.FileName.Split('_')[1];
        }

        string file = Server.MapPath("/") + "UploadFile\\SapData\\" + fileName;
        //Session["file"] = file;
        if (File.Exists(file))
        {
            File.Delete(file);
        }
        fileUpload.SaveAs(file);

        //var data = LoadHtml(file);
        //var data = LoadExcel(file, "sheet1");
        var data = ExcelToDataTable("sheet1", true, file);
        if (WebHelper.CheckIsNotEmpty(data))
        {
            string msg = SAP_Data_Validation(data);
            if (string.IsNullOrEmpty(msg))
            {
                btnSave.Enabled = true;
                Session["data"] = data;
                lbMessage.Text = "文件验证通过，请点击【" + btnSave.Text + "】保存到系统中！"; ;
            }
            else
            {
                File.Delete(file);
                lbMessage.Text = msg.ToString();
            }
        }
        else
        {
            File.Delete(file);
            lbMessage.Text = "该excel文件未能正确包含数据，请核实！";
        }
        
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        lbMessage.Text = "";
        var upLoadData = Session["data"] as DataTable;
        string excelFile = Session["excelFile"].ToString();
        string fileName = Session["fileName"].ToString();

        string currentUser = LoginUserInfo.CurrentUser.UserName;
        DateTime currentDT = DateTime.Now;
        DateTimeFormatInfo format = new DateTimeFormatInfo();

        DateTime tmpDate;

        format.ShortDatePattern = "yyyy/MM/dd";
        int allCount = 0;
        int nowCount = 0;
        string Msg = string.Empty;

        BFCompanyDicCollection bFCompanyDicCollection = new BFCompanyDicCollection();
        bFCompanyDicCollection.Query.Select(bFCompanyDicCollection.Query.CompanyName,bFCompanyDicCollection.Query.CompanyNo,bFCompanyDicCollection.Query.CompanyCode);
        DataTable dtCompanyDic = bFCompanyDicCollection.Query.LoadDataTable();
        DataColumn[] colCompanyDic = new DataColumn[] { dtCompanyDic.Columns["CompanyName"], dtCompanyDic.Columns["CompanyCode"] };
        dtCompanyDic.PrimaryKey = colCompanyDic;

        using (esTransactionScope scope = new esTransactionScope())
        {
            try
            {
                FinUploadLog finUploadLog = new FinUploadLog();
                finUploadLog.ExcelName = excelFile;
                finUploadLog.FileName = fileName;
                finUploadLog.CreatedBy = currentUser;
                finUploadLog.CreateDttm = currentDT;
                finUploadLog.Save();

                allCount = upLoadData.Rows.Count;
                for (int i = 0; i < upLoadData.Rows.Count; i++)
                {

                    if (i == 493)
                    {
                        string a = "1";
                    }
                    FinReceipt fr = new FinReceipt();
                    fr.IsSAP = true;
                    fr.IsConfirmed = false;

                    fr.LogicalSystem = upLoadData.Rows[i]["Logical System"].ToString();
                    fr.CompanyCode = upLoadData.Rows[i]["Company Code"].ToString();
                    fr.DocumentsType = upLoadData.Rows[i]["Document Type"].ToString();
                    fr.DocumentsNo = upLoadData.Rows[i]["Document Number"].ToString();
                    fr.Reference = upLoadData.Rows[i]["Reference"].ToString();
                    fr.DocumentHeaderText = upLoadData.Rows[i]["Document Header Text"].ToString();
                    fr.ReferenceKey = upLoadData.Rows[i]["Reference Key"].ToString();
                    if (IsDateTime(upLoadData.Rows[i]["Entry Date"].ToString()))
                    { fr.EntryDate = DateTime.Parse(upLoadData.Rows[i]["Entry Date"].ToString()); }

                    //if (upLoadData.Rows[i]["Reference Key"].ToString().Contains("E"))
                    //{
                    //    double baseNum = float.Parse(upLoadData.Rows[i]["Reference Key"].ToString().Substring(0, upLoadData.Rows[i]["Reference Key"].ToString().IndexOf("E")));
                    //    double power = float.Parse(upLoadData.Rows[i]["Reference Key"].ToString().Substring(upLoadData.Rows[i]["Reference Key"].ToString().IndexOf("E") + 1));
                    //    double num = Math.Pow(10, power);
                    //    double recentNum = baseNum * num;
                    //    recentNum=Math.Round(recentNum, 2);
                    //    fr.ReferenceKey = recentNum.ToString();
                    //}
                    //else
                    //{
                    //    fr.ReferenceKey =upLoadData.Rows[i]["Reference Key"].ToString();
                    //}


                    fr.RevesedBy = upLoadData.Rows[i][ "Reversed with"].ToString();
                    fr.ReferenceTransact = upLoadData.Rows[i]["Reference Transact."].ToString();
                    DateTime.TryParse(upLoadData.Rows[i]["Document Date"].ToString(), format, DateTimeStyles.None, out tmpDate);
                    fr.DocumentDate = tmpDate;
                    DateTime.TryParse(upLoadData.Rows[i]["Posting Date"].ToString(), format, DateTimeStyles.None, out tmpDate);
                    fr.PostingDate = tmpDate;
                    fr.VoucherDate = tmpDate.ToString("yyyy-MM-dd");
                    fr.StoreStartDate = DateTime.Parse(tmpDate.Year.ToString() + "-12-31");
                    fr.UserName = upLoadData.Rows[i]["User name"].ToString();


                    //fr.LogicalSystem = upLoadData.Rows[i][0].ToString();
                    //fr.CompanyCode = upLoadData.Rows[i][1].ToString();
                    //fr.DocumentsType = upLoadData.Rows[i][2].ToString();
                    //fr.DocumentsNo = upLoadData.Rows[i][3].ToString();
                    //fr.Reference = upLoadData.Rows[i][4].ToString();
                    //fr.DocumentHeaderText = upLoadData.Rows[i][5].ToString();
                    //fr.ReferenceKey = upLoadData.Rows[i][6].ToString();
                    //fr.RevesedBy = upLoadData.Rows[i][7].ToString();
                    //fr.ReferenceTransact = upLoadData.Rows[i][8].ToString();
                    //DateTime.TryParse(upLoadData.Rows[i][9].ToString(), format, DateTimeStyles.None, out tmpDate);
                    //fr.DocumentDate = tmpDate;
                    //DateTime.TryParse(upLoadData.Rows[i][10].ToString(), format, DateTimeStyles.None, out tmpDate);
                    //fr.PostingDate = tmpDate;
                    //fr.VoucherDate = tmpDate.ToString("yyyy-MM-dd");
                    //fr.StoreStartDate = DateTime.Parse(tmpDate.Year.ToString() + "-12-31");
                    //fr.UserName = upLoadData.Rows[i][11].ToString();
                    if (string.IsNullOrEmpty(fr.DocumentsNo))
                    { break; }

                    //FinReceiptCollection finReceiptCollection = new FinReceiptCollection();
                    //finReceiptCollection.Query.Where(finReceiptCollection.Query.StoreStartDate == fr.StoreStartDate, finReceiptCollection.Query.DocumentsNo == fr.DocumentsNo, finReceiptCollection.Query.CompanyCode == fr.CompanyCode);
                    //finReceiptCollection.Query.Load();
                    //if (finReceiptCollection.Count > 0)
                    //{
                    //    Msg += "Company Code为" + fr.CompanyCode + "，Documents Number为" + fr.DocumentsNo + "，PostingDate为" + tmpDate.ToString("yyyy-MM-dd") + "的凭证记录已经存在，未能导入，请核查\r\n";
                    //    continue;
                    //}

                    //fr.FirstStorageDate = DateTime.Now;
                    fr.DocumentSystem = txtHidDocSys.Value;
                    fr.CompanyName = txtHidCompanyName.Value;

                    DataRow drCompanyDic = dtCompanyDic.Rows.Find(new string[] { fr.CompanyName, fr.CompanyCode });
                    if (drCompanyDic != null)
                    {
                        fr.CompanyNo = drCompanyDic["CompanyNo"].ToString();
                    }

                    fr.StorageStatus = "S1";
                    fr.UpdatedBy = fr.CreatedBy = currentUser;
                    fr.UpdatedDttm = fr.CreatedDttm = currentDT;
                    fr.UploadId = finUploadLog.SapUploadId;
                    fr.Save();
                    nowCount++;
                }

                finUploadLog.LoadByPrimaryKey(finUploadLog.SapUploadId.Value);
                finUploadLog.AllCount = allCount;
                finUploadLog.NowCount = nowCount;
                finUploadLog.Save();

                scope.Complete();
                btnSave.Enabled = false;
                lbMessage.Text = "凭证共有"+allCount.ToString()+"条，导入"+nowCount.ToString()+ "条\r\n" +Msg;
                //WebHelper.CloseDialog(true);
            }
            catch (Exception ex)
            {
                lbMessage.Text = ex.Message;

            }
        }

    }
    protected void btnClose_Click(object sender, EventArgs e)
    {
        Session.Remove("data");
        WebHelper.CloseDialog(true);
    }

    #region 读取Html格式的Excel到DataSet
    protected DataSet LoadHtml(string fileName)
    {
        string connectionString = "Provider=Microsoft.Ace.OleDb.12.0;";
        connectionString += string.Format("Data Source={0};", fileName);
        connectionString += "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0;\"";
        //connectionString += "Extended Properties=\"HTML Import;HDR=Yes;IMEX=2;\"";
        OleDbConnection connection = new OleDbConnection(connectionString);
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        OleDbCommand command = new OleDbCommand();
        command.Connection = connection;
        adapter.SelectCommand = command;
        command.CommandText = "select * from [table]";
        DataSet dataSet = new DataSet();
        try
        {
            connection.Open();
            adapter.Fill(dataSet);
        }
        catch { throw; }
        finally
        {
            connection.Close();
        }
        return dataSet;
    }
    #endregion

    public static DataSet LoadExcel(string fileName, string sheetName)
    {


        string connectionString = "Provider=Microsoft.Ace.OleDb.12.0;";
        connectionString += string.Format("Data Source={0};", fileName);
        connectionString += "Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1;\"";
        OleDbConnection connection = new OleDbConnection(connectionString);
        connection.Open();
        DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        connection.Close();
        //if (string.IsNullOrEmpty(sheetName))
        //{
        //    if (Checker.IsNotEmpty(dt))
        //    {
        //        sheetName = dt.Rows[0][2].ToString();
        //    }
        //}
        //else
        //{
        //    if (sheetName.IndexOf("$") < 0)
        //    { sheetName = sheetName + "$"; }
        //}
        if (sheetName.IndexOf("$") < 0)
        { sheetName = sheetName + "$"; }
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        OleDbCommand command = new OleDbCommand();
        command.Connection = connection;
        adapter.SelectCommand = command;
        command.CommandText = string.Format("select * from [{0}]", sheetName);
        DataSet dataSet = new DataSet();
        try
        {
            connection.Open();
            /*
            DataTable schemaTable = connection.GetOleDbSchemaTable(
            OleDbSchemaGuid.Tables,
            new object[] { null, null, null, "TABLE" });
             * */
            adapter.Fill(dataSet);
            return dataSet;
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            connection.Close();
        }
    }



    private IWorkbook workbook = null;
    const string FAIL_DUPLICATE_TEMPLATE = "第{0}行，{1}:在文件中重复.";
    const string FAIL_DUPLICATE_DB_TEMPLATE = "第{0}行，{1}:已存在数据库中.";
    const string FAIL_EMPTY_TEMPLATE = "第{0}行，{1}:不能为空.<br/>";
    const string FAIL_FORMAT_TEMPLATE = "第{0}行，{1}:数据格式不正确.<br/>";
    string[] columnName = new string[] { "Logical System", "Company Code", "Document Type", "Document Number", "Reference", "Document Header Text", "Reference Key", "Reversed with", "Reference Transact.", "Document Date", "Posting Date", "User name","Entry Date" };
    
    /// <summary>
    /// 将excel中的数据导入到DataTable中
    /// </summary>
    /// <param name="sheetName">excel工作薄sheet的名称</param>
    /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
    /// <returns>返回的DataTable</returns>
    public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string fileName)
    {
        ISheet sheet = null;
        DataTable data = new DataTable();
        int startRow = 0;
        fileName = fileName.ToLower();

        try
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                if (string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    if (hsColumn.ContainsKey(cellValue))
                                    {
                                        hsColumn[cellValue] = i;
                                        DataColumn column = new DataColumn(cellValue);
                                        data.Columns.Add(column);
                                    }
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        //if (i == 572)
                        //{
                        //    string aaa = "temp";
                        //}
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            ICell cellTemp = row.GetCell(j);
                            if (cellTemp != null) //同理，没有数据的单元格都默认是null
                            {
                                switch (cellTemp.CellType)
                                {
                                    case CellType.Blank: //空数据类型处理
                                        dataRow[j] = "";
                                        break;
                                    case CellType.String: //字符串类型
                                        dataRow[j] = cellTemp.StringCellValue;
                                        break;
                                    case CellType.Numeric: //数字类型  
                                        if (HSSFDateUtil.IsCellDateFormatted(cellTemp))
                                        {

                                            //NPOI.XSSF.UserModel.XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
                                            //NPOI.XSSF.UserModel.XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
                                            //style.DataFormat = format.GetFormat("yyyy-mm-dd");
                                            //cellTemp.CellStyle = style;

                                            dataRow[j] = HSSFDateUtil.GetJavaDate(cellTemp.NumericCellValue);
                                        }
                                        else
                                        {
                                            dataRow[j] = cellTemp.NumericCellValue;
                                        }
                                        break;
                                    case CellType.Formula:
                                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
                                        dataRow[j] = e.Evaluate(cellTemp).StringValue;
                                        break;
                                    default:
                                        dataRow[j] = "";
                                        break;
                                }
                                //dataRow[j] = row.GetCell(j).ToString();
                                //if (j == 9 || j == 10)
                                //{
                                //    dataRow[j] = row.GetCell(j).DateCellValue; 
                                //}
                                //else
                                //{
                                //    dataRow[j] = row.GetCell(j).ToString();
                                //}
                            }

                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
        }
        catch (Exception ex)
        {
            lbMessage.Text = "读取数据失败，错误原因: " + ex.Message;
            //Console.WriteLine("Exception: " + ex.Message);
            return null;
        }
    }
    StringBuilder failmsg = new StringBuilder();
    /// <summary>
    /// validation the data
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    /// <summary>
    /// validation the data
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    /// 
    string SAP_Data_Validation(DataTable data)
    {
        string failmsg = string.Empty;
        string[] columns = { "Posting Date", "Document Date", "Entry Date" };
        for (int i = 0; i < columns.Length; i++)
        {
            foreach (DataColumn dc in data.Columns)
            {
                if (dc.ColumnName == columns[i])
                {
                    int rowIndex = dc.Ordinal;
                    for (int j = 0; j < data.Rows.Count; j++)
                    {
                        if (!IsDateTime(data.Rows[j][rowIndex].ToString()))
                        {
                            failmsg += "第" + (j + 1).ToString() + "行的" + dc.ColumnName + "不是正确的日期格式！\r\n";
                        }
                    }

                }
            }
        }


        if (string.IsNullOrEmpty(failmsg))
        {
            for (int i = 0; i < data.Rows.Count; i++)
            {
                DateTime tmpDate = DateTime.Parse(DateTime.Parse(data.Rows[i]["Posting Date"].ToString()).Year.ToString() + "-12-31");
                FinReceiptCollection finReceiptCollection = new FinReceiptCollection();
                finReceiptCollection.Query.Where(finReceiptCollection.Query.StoreStartDate == tmpDate,
                    finReceiptCollection.Query.DocumentsNo == data.Rows[i]["Document Number"].ToString(),
                    finReceiptCollection.Query.CompanyCode == data.Rows[i]["Company Code"].ToString(),
                    finReceiptCollection.Query.IsDeleted==false);
                finReceiptCollection.Query.Load();
                if (finReceiptCollection.Count > 0)
                {
                    failmsg += "Company Code为" + data.Rows[i]["Company Code"].ToString()
                        + "，Documents Number为" + data.Rows[i]["Document Number"].ToString()
                        + "，PostingDate为" + tmpDate.ToString("yyyy-MM-dd") + "的凭证记录已经存在，不能导入，请核查\r\n";

                }
            }
        }
        return failmsg;
    }

    //bool SAP_Data_Validation(DataTable data)
    //{
    //    failmsg = new StringBuilder();
    //    bool retValue = true;

    //    DateTimeFormatInfo format = new DateTimeFormatInfo();
    //    format.ShortDatePattern = "yyyy/MM/dd";

    //    DateTime dateCheck;
    //    List<string> docNoList = new List<string>();
    //    for (int i = 0; i < data.Rows.Count; i++)
    //    {
    //        string emptyCol = string.Empty;
    //        string formatCol = string.Empty;
    //        bool isExists = docNoList.Exists(r => r == data.Rows[i][3].ToString());
    //        if (isExists)
    //        {
    //            failmsg.AppendFormat(FAIL_DUPLICATE_TEMPLATE, i + 1, emptyCol.TrimEnd(',') + "-" + data.Rows[i][1].ToString());
    //        }
    //        //else
    //        //{

    //        //    FinReceipt fr = new FinReceipt();
    //        //    fr.Query.Where(fr.Query.DocumentsNo == data.Rows[i][1].ToString().Trim());
    //        //    fr.Query.Load();
    //        //    if (fr.es.HasData)
    //        //    {
    //        //        failmsg.AppendFormat(FAIL_DUPLICATE_DB_TEMPLATE, i + 1, emptyCol.TrimEnd(',') + "-" + data.Rows[i][1].ToString());

    //        //    }
    //        //    else
    //        //    {
    //        //        docNoList.Add(data.Rows[i][1].ToString());
    //        //    }

    //        //}
    //        for (int j = 0; j < 11; j++)
    //        {
    //            string value = data.Rows[i][j].ToString();
    //            if (string.IsNullOrEmpty(value) && (j == 1 || j == 2 || j == 3 || j == 8 || j == 9 || j == 10 || j == 11))
    //            {
    //                emptyCol = columnName[j] + ",";
    //            }
    //            else
    //            {
    //                if (j == 9 || j == 10)
    //                {
    //                    if (!DateTime.TryParse(value, format, DateTimeStyles.None, out dateCheck))
    //                    {
    //                        formatCol = columnName[j] + ",";
    //                    }
    //                }
    //                //else if (j == 12)
    //                //{
    //                //    if (!value.Equals("S1") || !value.Equals("S2") || !value.Equals("S3") || !value.Equals("S4") || !value.Equals("S5") || !value.Equals("S6"))
    //                //    {
    //                //        formatCol = columnName[j] + ",";
    //                //    }
    //                //}
    //            }
    //        }
    //        if (emptyCol.Length > 0)
    //        {
    //            failmsg.AppendFormat(FAIL_EMPTY_TEMPLATE, i + 1, emptyCol.TrimEnd(','));
    //        }
    //        if (formatCol.Length > 0)
    //        {
    //            failmsg.AppendFormat(FAIL_FORMAT_TEMPLATE, i + 1, formatCol.TrimEnd(','));
    //        }
    //    }

    //    if (failmsg.Length > 0)
    //    {
    //        retValue = false;
    //    }


    //    return retValue;
    //}


    /// <summary>
    /// 验证输入为Datetime
    /// </summary>
    /// <param name="str_number"></param>
    /// <returns></returns>
    private bool IsDateTime(string str)
    {
        DateTime temp;
        return DateTime.TryParse(str, out temp);
    }
}